package com.ziyear.dao.impl;

import com.ziyear.common.util.JDBCUtil;
import com.ziyear.dao.TrainDao;
import com.ziyear.domain.Train;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

public class TrainDaoImpl implements TrainDao {

    private Connection conn = null;
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;

    @Override
    public int add(Train train) throws SQLException {
        String sql = "insert into train (trainnumber,startstation,endstation,starttime,endtime,addtime," +
                "shangwu,shangwuprice,yideng,yidengprice,erdeng,erdengprice) values (?,?,?,?,?,?,?,?,?,?,?,?)";

        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
        pstmt.setObject(1, train.getTrainNumber());
        pstmt.setObject(2, train.getStartStation());
        pstmt.setObject(3, train.getEndStation());
        pstmt.setObject(4, train.getStartTime());
        pstmt.setObject(5, train.getEndTime());
        pstmt.setObject(6, train.getAddTime());
        pstmt.setObject(7, train.getShangWu());
        pstmt.setObject(8, train.getShangWuPrice());
        pstmt.setObject(9, train.getYiDeng());
        pstmt.setObject(10, train.getYiDengPrice());
        pstmt.setObject(11, train.getErDeng());
        pstmt.setObject(12, train.getErDengPrice());
        int recordNumber = pstmt.executeUpdate();
        ResultSet generatedKeys = pstmt.getGeneratedKeys();
        while (generatedKeys.next()) {
            int generateKey = generatedKeys.getInt(1);
            train.setTrainId(generateKey);
        }
        pstmt.close();
        JDBCUtil.close(conn);
        return recordNumber;
    }

    @Override
    public int delete(Integer trainId) throws SQLException {
        String sql = "delete from train where trainid=?";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, trainId);
        int recordNumber = pstmt.executeUpdate();
        pstmt.close();
        JDBCUtil.close(conn);
        return recordNumber;
    }

    /*
     * @Override public int update(Train train) throws SQLException { conn =
     * JDBCUtil.getConnection(); queryRunner = new QueryRunner(); int recordNumber =
     * queryRunner.update(conn,
     * "update train set trainnumber=?,startstation=?,endstation=?,starttime=?,endtime=?,price=?,seatnumber=?,addtime=? where trainid=?"
     * , train.getTrainNumber(),train.getStartStation(),train.getEndStation(),train.
     * getStartTime(),train.getEndTime(),train.getPrice(),train.getSeatNumber(),
     * train.getAddTime(),train.getTrainId()); JDBCUtil.close(conn); return
     * recordNumber; }
     */

    @Override
    public int update(Train train) throws SQLException {
        String sql = "update train set trainnumber=?,startstation=?,endstation=?,starttime=?,endtime=?," +
                "shangwu=?,shangwuprice=?,yideng=?,yidengprice=?,erdeng=?,erdengprice=? where trainid=?";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, train.getTrainNumber());
        pstmt.setObject(2, train.getStartStation());
        pstmt.setObject(3, train.getEndStation());
        pstmt.setObject(4, train.getStartTime());
        pstmt.setObject(5, train.getEndTime());
        pstmt.setObject(6, train.getShangWu());
        pstmt.setObject(7, train.getShangWuPrice());
        pstmt.setObject(8, train.getYiDeng());
        pstmt.setObject(9, train.getYiDengPrice());
        pstmt.setObject(10, train.getErDeng());
        pstmt.setObject(11, train.getErDengPrice());
        pstmt.setObject(12, train.getTrainId());
        int recordNumber = pstmt.executeUpdate();
        pstmt.close();
        JDBCUtil.close(conn);
        return recordNumber;
    }

    @Override
    public List<Train> getAll() throws SQLException {
        String sql = "select * from train";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();
        List<Train> list = new ArrayList<Train>();
        while (rs.next()) {
            int trainId = rs.getInt("trainId");
            String trainNumber = rs.getString("trainNumber");
            String startStation = rs.getString("startStation");
            String endStation = rs.getString("endStation");
            String startTime = rs.getString("startTime");
            String endTime = rs.getString("endTime");
            Timestamp addTime = rs.getTimestamp("addTime");
            int shangwu = rs.getInt("shangwu");
            BigDecimal shangwuprice = rs.getBigDecimal("shangwuprice");
            int yideng = rs.getInt("yideng");
            BigDecimal yidengprice = rs.getBigDecimal("yidengprice");
            int erdeng = rs.getInt("erdeng");
            BigDecimal erdengprice = rs.getBigDecimal("erdengprice");
            Train train = new Train(trainId, trainNumber, startStation, endStation, startTime, endTime,
                    addTime, shangwu, shangwuprice, yideng, yidengprice, erdeng, erdengprice);
            list.add(train);
        }
        rs.close();
        pstmt.close();
        JDBCUtil.close(conn);
        return list;
    }

    @Override
    public Train getById(Integer trainId) throws SQLException {
        String sql = "select * from train where trainid=?";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, trainId);
        rs = pstmt.executeQuery();
        Train train = null;
        while (rs.next()) {
            String trainNumber = rs.getString("trainNumber");
            String startStation = rs.getString("startStation");
            String endStation = rs.getString("endStation");
            String startTime = rs.getString("startTime");
            String endTime = rs.getString("endTime");
            Timestamp addTime = rs.getTimestamp("addTime");
            int shangwu = rs.getInt("shangwu");
            BigDecimal shangwuprice = rs.getBigDecimal("shangwuprice");
            int yideng = rs.getInt("yideng");
            BigDecimal yidengprice = rs.getBigDecimal("yidengprice");
            int erdeng = rs.getInt("erdeng");
            BigDecimal erdengprice = rs.getBigDecimal("erdengprice");
            train = new Train(trainId, trainNumber, startStation, endStation, startTime, endTime,
                    addTime, shangwu, shangwuprice, yideng, yidengprice, erdeng, erdengprice);
        }
        rs.close();
        pstmt.close();
        JDBCUtil.close(conn);
        return train;
    }

    @Override
    public List<Train> getByTrainNumber(String trainNumber) throws SQLException {
        String sql = "select * from train where trainnumber=?";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, trainNumber);
        rs = pstmt.executeQuery();
        List<Train> list = new ArrayList<Train>();
        while (rs.next()) {
            int trainId = rs.getInt("trainId");
            String startStation = rs.getString("startStation");
            String endStation = rs.getString("endStation");
            String startTime = rs.getString("startTime");
            String endTime = rs.getString("endTime");
            Timestamp addTime = rs.getTimestamp("addTime");
            int shangwu = rs.getInt("shangwu");
            BigDecimal shangwuprice = rs.getBigDecimal("shangwuprice");
            int yideng = rs.getInt("yideng");
            BigDecimal yidengprice = rs.getBigDecimal("yidengprice");
            int erdeng = rs.getInt("erdeng");
            BigDecimal erdengprice = rs.getBigDecimal("erdengprice");
            Train train = new Train(trainId, trainNumber, startStation, endStation, startTime, endTime,
                    addTime, shangwu, shangwuprice, yideng, yidengprice, erdeng, erdengprice);
            list.add(train);
        }
        rs.close();
        pstmt.close();
        JDBCUtil.close(conn);
        return list;
    }

    @Override
    public List<Train> getByStartEndStation(String startStation, String endStation, String startTime)
            throws SQLException {
        String sql = "select * from train where startStation=? and endStation=? and startTime like \"%\"?\"%\"";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1, startStation);
        pstmt.setObject(2, endStation);
        pstmt.setObject(3, startTime);
        rs = pstmt.executeQuery();
        List<Train> list = new ArrayList<Train>();
        while (rs.next()) {
            int trainId = rs.getInt("trainId");
            String trainNumber = rs.getString("trainNumber");
            String endTime = rs.getString("endTime");
            Timestamp addTime = rs.getTimestamp("addTime");
            int shangwu = rs.getInt("shangwu");
            BigDecimal shangwuprice = rs.getBigDecimal("shangwuprice");
            int yideng = rs.getInt("yideng");
            BigDecimal yidengprice = rs.getBigDecimal("yidengprice");
            int erdeng = rs.getInt("erdeng");
            BigDecimal erdengprice = rs.getBigDecimal("erdengprice");
            Train train = new Train(trainId, trainNumber, startStation, endStation, startTime, endTime,
                    addTime, shangwu, shangwuprice, yideng, yidengprice, erdeng, erdengprice);
            list.add(train);
        }
        rs.close();
        pstmt.close();
        JDBCUtil.close(conn);
        return list;
    }

    @Override
    public List<Train> getAllStartStation() throws SQLException {
        String sql = "select distinct startstation from train";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();
        List<Train> list = new ArrayList<Train>();

        while (rs.next()) {
            String startStation = rs.getString("startStation");
            Train train = new Train();
            train.setStartStation(startStation);
            list.add(train);
        }
        rs.close();
        pstmt.close();
        JDBCUtil.close(conn);
        return list;
    }

    @Override
    public List<Train> getAllEndStation() throws SQLException {
        String sql = "select distinct endstation from train";
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();
        List<Train> list = new ArrayList<Train>();
        while (rs.next()) {
            String endStation = rs.getString("endStation");
            Train train = new Train();
            train.setEndStation(endStation);
            list.add(train);
        }
        rs.close();
        pstmt.close();
        JDBCUtil.close(conn);
        return list;
    }

}
